'use strict';

const Service = require('egg').Service;

/**
 * 部门服务类
 * 处理部门相关的数据库操作和业务逻辑
 */
class DepartmentService extends Service {
  /**
   * 获取所有部门
   * 返回未删除的部门列表及其人数，按排序值升序排列
   * @returns {Promise<Array>} 部门列表
   */
  async findAll() {
    const sql = `
      SELECT 
        d.*,
        COUNT(p.person_id) as person_count
      FROM 
        tb_department d
        LEFT JOIN tb_person p ON d.department_id = p.department_id AND p.is_deleted = 0
      WHERE 
        d.is_deleted = 0
      GROUP BY 
        d.department_id
      ORDER BY 
        d.sort ASC, d.department_id ASC
    `;
    const result = await this.app.mysql.query(sql);
    return result;
  }

  /**
   * 根据ID获取部门
   * 返回部门信息及其人数
   * @param {number} id - 部门ID
   * @returns {Promise<Object|null>} 部门信息，不存在时返回null
   */
  async findById(id) {
    const sql = `
      SELECT 
        d.*,
        COUNT(p.person_id) as person_count
      FROM 
        tb_department d
        LEFT JOIN tb_person p ON d.department_id = p.department_id AND p.is_deleted = 0
      WHERE 
        d.department_id = ? AND d.is_deleted = 0
      GROUP BY 
        d.department_id
    `;
    const result = await this.app.mysql.query(sql, [id]);
    return result[0];
  }

  /**
   * 检查部门名称是否已存在
   * @param {string} departmentName - 部门名称
   * @param {number} [excludeId] - 排除的部门ID（用于更新时检查）
   * @returns {Promise<boolean>} 是否存在
   */
  async checkNameExist(departmentName, excludeId = null) {
    let sql = 'SELECT COUNT(*) as count FROM tb_department WHERE department_name = ? AND is_deleted = 0';
    const params = [departmentName];

    if (excludeId) {
      sql += ' AND department_id != ?';
      params.push(excludeId);
    }

    const result = await this.app.mysql.query(sql, params);
    return result[0].count > 0;
  }

  /**
   * 创建部门
   * @param {Object} data - 部门数据
   * @param {string} data.department_name - 部门名称
   * @param {number} [data.sort] - 排序值
   * @returns {Promise<Object|null>} 创建的部门信息，名称重复时返回null
   */
  async create(data) {
    // 检查部门名称是否已存在
    const exists = await this.checkNameExist(data.department_name);
    if (exists) {
      return null;
    }

    const result = await this.app.mysql.insert('tb_department', {
      department_name: data.department_name,
      sort: data.sort,
      is_deleted: 0,
    });
    
    return await this.findById(result.insertId);
  }

  /**
   * 更新部门
   * @param {number} id - 部门ID
   * @param {Object} data - 更新数据
   * @param {string} data.department_name - 部门名称
   * @param {number} [data.sort] - 排序值
   * @returns {Promise<Object|{error: string}|null>} 更新后的部门信息，不存在返回null，名称重复返回错误对象
   */
  async update(id, data) {
    // 先检查记录是否存在且未删除
    const department = await this.findById(id);
    if (!department) {
      return null;
    }

    // 检查新名称是否与其他部门重复
    const exists = await this.checkNameExist(data.department_name, id);
    if (exists) {
      return { error: 'duplicate_name' };
    }

    // 执行更新
    await this.app.mysql.update('tb_department', {
      department_name: data.department_name,
      sort: data.sort,
    }, {
      where: { department_id: id, is_deleted: 0 }
    });
    
    return await this.findById(id);
  }

  /**
   * 软删除部门
   * 如果部门下有人员则不允许删除
   * @param {number} id - 部门ID
   * @returns {Promise<Object|{error: string}|null>} 删除的部门信息，不存在返回null，有人员时返回错误信息
   */
  async delete(id) {
    // 先检查记录是否存在且未删除
    const department = await this.findById(id);
    if (!department) {
      return null;
    }

    // 检查部门下是否有人员
    const hasPersons = await this.checkHasPersons(id);
    if (hasPersons) {
      return { error: 'has_persons' };
    }

    // 执行软删除
    await this.app.mysql.update('tb_department', {
      is_deleted: 1,
    }, {
      where: { department_id: id, is_deleted: 0 }
    });

    return department;
  }

  /**
   * 检查部门下是否有人员
   * @param {number} departmentId - 部门ID
   * @returns {Promise<boolean>} 是否有人员
   */
  async checkHasPersons(departmentId) {
    const result = await this.app.mysql.query(
      'SELECT COUNT(*) as count FROM tb_person WHERE department_id = ? AND is_deleted = 0',
      [departmentId]
    );
    return result[0].count > 0;
  }
}

module.exports = DepartmentService;